package unify.fileio;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Logger;

import unify.ShowLibrary;
import unify.data.Episode;
import unify.data.Feed;
import unify.data.Search;
import unify.data.Season;
import unify.data.Settings;
import unify.data.Show;


import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.logger.LocalLog;
import com.j256.ormlite.stmt.PreparedQuery;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

public class Database {

	public static String dbFileName = "UnifyDatabase";
	private final static Logger LOGGER = Logger.getLogger(Database.class.getName());

	public static void initializeDB() {
		LOGGER.info("Initialized database");
		System.setProperty(LocalLog.LOCAL_LOG_LEVEL_PROPERTY, "error");
		try {
			ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
			TableUtils.createTableIfNotExists(connectionSource, Show.class);
			TableUtils.createTableIfNotExists(connectionSource, Season.class);
			TableUtils.createTableIfNotExists(connectionSource, Episode.class);
			TableUtils.createTableIfNotExists(connectionSource, Feed.class);
			TableUtils.createTableIfNotExists(connectionSource, Settings.class);
			TableUtils.createTableIfNotExists(connectionSource, Search.class);
			connectionSource.close();
		} catch (SQLException e) {
			LOGGER.severe("SQLException: " + e.toString());
			System.exit(1);
		}
	}

	public static void saveData() {
		try {
			ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
			Dao<Show, Integer> showDao = DaoManager.createDao(connectionSource, Show.class);
			Dao<Season, Integer> seasonDao = DaoManager.createDao(connectionSource, Season.class);
			Dao<Episode, Integer> episodeDao = DaoManager.createDao(connectionSource, Episode.class);
			int numShows = 0;
			int numSeasons = 0;
			int numEpisodes = 0;
			ArrayList<Show> shows = ShowLibrary.getInstance().shows;
			for(Show show : shows) {
				showDao.createOrUpdate(show);
				numShows++;
				for(Season season : show.seasons) {
					seasonDao.createOrUpdate(season);
					numSeasons++;
					for(Episode episode : season.getEpisodes()) {
						episodeDao.createOrUpdate(episode);
						numEpisodes++;
					}
				}
			}
			for(Show dbShow : showDao.queryForAll()) {
				Show myShow = ShowLibrary.getInstance().getShow(dbShow.getTitle());
				if(myShow==null) {
					for(Season season : dbShow.seasons) {
						episodeDao.delete(season.getEpisodes());
					}
					seasonDao.delete(dbShow.seasons);
					showDao.delete(dbShow);
				}
			}
			Dao<Settings, Integer> settingsDao = DaoManager.createDao(connectionSource, Settings.class);
			Dao<Feed, Integer> feedDao = DaoManager.createDao(connectionSource, Feed.class);
			Dao<Search, Integer> searchDao = DaoManager.createDao(connectionSource, Search.class);
			Settings settings = Settings.getInstance();
			for(Feed feed : settings.getFeeds()) {
				feedDao.createOrUpdate(feed);
			}
			for(Search search : settings.getSearches()) {
				searchDao.createOrUpdate(search);
			}
			settingsDao.createOrUpdate(settings);
			LOGGER.fine("Stored " + numShows + " shows, " + numSeasons + " seasons, and " + numEpisodes + " episodes in database.");
			connectionSource.close();
		} catch (SQLException e) {
			LOGGER.severe("SQLException: " + e.toString());
			System.exit(1);
		}
	}

	public static void loadShows() {
		try {
			ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
			Dao<Show, Integer> showDao = DaoManager.createDao(connectionSource, Show.class);
			QueryBuilder<Show, Integer> queryBuilder = showDao.queryBuilder().orderBy("title", true);
			PreparedQuery<Show> preparedQuery = queryBuilder.prepare();
			ShowLibrary.getInstance().shows.clear();
			ShowLibrary.getInstance().shows.addAll(showDao.query(preparedQuery));
			connectionSource.close();
		} catch (SQLException e) {
			LOGGER.severe("SQLException: " + e.toString());
			System.exit(1);
		}
		int numShows = 0;
		int numSeasons = 0;
		int numEpisodes = 0;
		for(int i=0;i<ShowLibrary.getInstance().shows.size();i++) {
			numShows++;
			for(Season season : ShowLibrary.getInstance().shows.get(i).seasons) {
				numSeasons++;
				numEpisodes = numEpisodes + season.getEpisodes().size();
			}
		}
		LOGGER.fine("Loaded " + numShows + " shows, " + numSeasons + " seasons, and " + numEpisodes + " episodes from database to memory.");
	}

	public static Settings getSettings() {
		Settings settings = null;
		try {
			ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
			Dao<Settings, Integer> settingsDao = DaoManager.createDao(connectionSource, Settings.class);
			QueryBuilder<Settings, Integer> queryBuilder = settingsDao.queryBuilder();
			queryBuilder.where().isNotNull("databaseId");
			PreparedQuery<Settings> preparedQuery = queryBuilder.prepare();
			settings = settingsDao.queryForFirst(preparedQuery);
			settingsDao.update(settings);
			connectionSource.close();
		} catch (SQLException e) {
			LOGGER.severe("SQLException: " + e.toString());
			System.exit(1);
		}
		LOGGER.fine("Loaded settings from database");
		return settings;
	}

	public static void clear() {
		try {
		ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
		TableUtils.dropTable(connectionSource, Show.class, true);
		TableUtils.dropTable(connectionSource, Season.class, true);
		TableUtils.dropTable(connectionSource, Episode.class, true);
		TableUtils.dropTable(connectionSource, Settings.class, true);
		TableUtils.dropTable(connectionSource, Feed.class, true);
		TableUtils.dropTable(connectionSource, Search.class, true);
		} catch (SQLException e) {
			LOGGER.severe("SQLException: " + e.toString());
			System.exit(1);
		}
	}

}
